package com.wpi.iCoach;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * The Location Database Manager maintains the database used by all activities in this
 * application. It provides a means for these activities to interact with the database
 * indirectly. Currently one function for a single query is available. Functions to 
 * insert entries in the database also exist. 
 * 
 * @author Kenneth J. Loomis (kjloomis),
 *         Xizhao Chen (xchen)
 * @version June 2012
 */
public class LocDBMgr {

	/*
	 * Database constants. Likely to change so not fully documented here. 
	 */
	public static final String[] LOC_TYPES = { "Housing", "Benefits", "Health", "Education" };
	
	private static final String DB_NAME = "LocationDB";
	private static final int DB_VERSION = 17;
	public static int DB_DATA_VERSION;
	
	private static final String DB_LOC_TABLE = "locTable";
	
	public static final String KEY_ROW_ID = "_id";
	public static final String KEY_LOC_NAME = "loc_name";
	public static final String KEY_LAT = "lat";
	public static final String KEY_LON = "lon";
	public static final String KEY_LOC_TYPE = "type";
	public static final String KEY_RANK = "rank";
	public static final String KEY_VISITS = "visits";
	public static final String INDEX = "region_index";
	
	private static final String DB_VISIT_TABLE = "visitTable";
	
	public static final String KEY_LOC_ID = "loc_id";
	public static final String KEY_DATE = "v_date";
	public static final String KEY_LIKE = "likeness";
	public static final String KEY_SUCCESS = "success";
	
	/** The database helper class. */
	private static DBHelper dbh;
	
	/** The context of the Application */
	private static Context cntxt;
	
	/** An SQLite database used to persistently store location data. */
	private static SQLiteDatabase db;
	
	/**
	 * The database helper that is used to create a database (the tables within
	 * the database), and upgrade a database from an old version to a new version.
	 * (Currently, no data is migrated upon a database version upgrade.)
	 * 
	 * @author Kenneth J. Loomis (kjloomis)
	 * @version June 2012
	 */
	private static class DBHelper extends SQLiteOpenHelper {

		/**
		 * Constructor. Relies in the underlying SQLiteOpenHelper constructor
		 * @param context
		 */
		public DBHelper(Context context ) {
			super(context, DB_NAME, null, DB_VERSION);
			// TODO Auto-generated constructor stub
		}
		
		/**
		 * Called when a database has not previously been created. This
		 * should only occur the first time the application is ran. This database
		 * persists on updates of the application.
		 * @param db (an SQLiteDatabase to be created) 
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
						
			db.execSQL( "CREATE TABLE " + DB_LOC_TABLE + " (" + 
						KEY_ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
						KEY_LOC_NAME + " TEXT NOT NULL, " +
						KEY_LAT + " REAL NOT NULL," + 
						KEY_LON + " REAL NOT NULL," +
						KEY_LOC_TYPE + " INTEGER NOT NULL," +
						KEY_VISITS + " INTEGER NOT NULL," +
						KEY_RANK + " REAL NOT NULL );" );
			
			db.execSQL( "CREATE TABLE " + DB_VISIT_TABLE + " (" + 
						KEY_ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
						KEY_LOC_ID + " INTEGER NOT NULL, " +
						KEY_DATE + " STRING NOT NULL," + 
						KEY_LIKE + " INTEGER NOT NULL," +
						KEY_SUCCESS + " INTEGER NOT NULL );" );
			
			DB_DATA_VERSION = 0; // There is no data in the database
			
		}

		/**
		 * Called when a previously created database has a version that is found
		 * to be less than the currently database version. The previous database
		 * tables are dropped and replaced with new tables.
		 * (Alternately, data migration could be incorporated here, but it has not
		 * been done as it is version specific.)
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL( "DROP TABLE IF EXISTS " + DB_LOC_TABLE + " ;" );
			db.execSQL( "DROP TABLE IF EXISTS " + DB_VISIT_TABLE + " ;" );
			onCreate ( db );
		}
		
	}
	
	public LocDBMgr ( Context c )
	{
		if ( LocDBMgr.cntxt == null )
			LocDBMgr.cntxt = c;
	}
	
	public LocDBMgr Open ( )
	{
		dbh = new DBHelper ( cntxt );
		db = dbh.getWritableDatabase ( );
		return this;
	}
	
	public void Close ( )
	{
		dbh.close();
	}
	
	public long AddToLocationDB(float gps1, float gps2, String name) throws SQLiteException
	{
		// TODO Auto-generated method stub
		ContentValues tmpCV = new ContentValues ( );
		
		tmpCV.put ( KEY_LOC_NAME, name );
		tmpCV.put ( KEY_LAT, gps1 );
		tmpCV.put ( KEY_LON, gps2 );
		//tmpCV.put ( KEY_REGION, 1 );
		tmpCV.put ( KEY_VISITS, 0 );
		tmpCV.put ( KEY_RANK, 0 );
		return db.insert( DB_LOC_TABLE, null, tmpCV );
		
	}
	
	public long BatchAddLocations( ContentValues[] cvs )
	{
		for ( int iter = 0; iter < cvs.length; iter++ )
		{
			if ( cvs[ iter ] != null )
				db.insert( DB_LOC_TABLE, null, cvs[ iter ] );
		}
		DB_DATA_VERSION = 1;
		return cvs.length;
	}

	public LocationEntry[] GetTopK ( double lat, double lon, String[] types, int k, int sort )
	{		
		LocationEntry locList2 [] = null;
		List<LocationEntry> locList = new ArrayList<LocationEntry>();
		
		String columns[] = { KEY_ROW_ID, KEY_LOC_NAME, KEY_LAT, KEY_LON, KEY_VISITS, KEY_RANK };
		Cursor cursor = db.query ( DB_LOC_TABLE, columns, KEY_LOC_TYPE + "=?", types, null, null, null );

		for ( cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			double gps1 = cursor.getFloat( cursor.getColumnIndex( KEY_LAT ) );
			double gps2 = cursor.getFloat( cursor.getColumnIndex( KEY_LON ) );
			double dLat = Math.toRadians(gps1-lat);
			double dLon = Math.toRadians(gps2-lon);
			double distance = Math.pow( Math.sin(  dLat/2 ), 2 )  + Math.pow( Math.sin(  dLon/2 ), 2 ) *
							  Math.cos( Math.toRadians( lat )) * Math.cos( Math.toRadians( lon ));
			distance = 2 * Math.atan2 ( Math.sqrt( distance ), Math.sqrt( 1-distance ) ) * 3961.1; 				
			locList.add( new LocationEntry ( (float) gps1, (float) gps2,
											 cursor.getString( cursor.getColumnIndex( KEY_LOC_NAME ) ),
											 (float) distance,
											 cursor.getInt( cursor.getColumnIndex( KEY_VISITS ) ),
											 cursor.getFloat( cursor.getColumnIndex( KEY_RANK ) ) ) );
			
		}
		// Be sure to close the cursor
		cursor.close();
		
		
		if ( locList.size() > 0)
		{
			//Collections.sort( locList );
			Collections.sort( locList, LocationEntry.getComparator( sort ) );
			//display k results
			if ( locList.size() > k )
				locList = locList.subList(0, k);
		
			locList2 = new LocationEntry[ locList.size() ];
			locList.toArray( locList2 ); 
		}
			
		return locList2;
		
	}
	
}
